package com.persistence.dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.model.Coleccion;
import com.model.Genero;
import com.model.LineaProduccion;
import com.model.Modelo;
import com.model.TipoModelo;
import com.persistence.dao.DAOException;
import com.persistence.dao.DAOManagerMain;
import com.persistence.dao.interfaces.DAOModelo;

public class MySQLDAOModelo implements DAOModelo {
	
	private Modelo modelo = null;
	
	
	public MySQLDAOModelo() {
	}

	public int insert(Modelo modelo) throws DAOException {
		Connection connection = null;
		ResultSet rs = null;
		PreparedStatement stmt = null;

		try {
			connection = DAOManagerMain.manager().getConnection();
			stmt = connection.prepareStatement("insert into Modelo ("
					+ "Id_Modelo, Nombre, Importado, Id_Tipo, "
					+ "Id_LineaProd, Id_Genero, fechaCreacion, "
					+ "coleccion, fechaLanzamiento) " + "values (?, ?, ?, ?, ?, ?, ?, ?, ?) ");

			stmt.setLong(1, modelo.getNroModelo());
			stmt.setString(2, modelo.getNombre());
			stmt.setBoolean(3, modelo.isImportado());
			stmt.setLong(4, modelo.getTipoModelo().getNroTipoModelo());
			stmt.setLong(5, modelo.getLineaProduccion().getNroLinea());
			stmt.setLong(6, modelo.getGenero().getNroGenero());
			stmt.setString(7, modelo.getFechaCreacion());
			stmt.setLong(8, modelo.getColeccion().getNroColeccion());
			stmt.setString(9, modelo.getFechaLanzamiento());
			stmt.executeUpdate();
			
			return 1;
		} catch (SQLException sqle) {
			throw new DAOException(sqle);

		} finally {
			DAOManagerMain.manager().closeAll(connection, stmt, rs);
		}
	}

	public int update(Modelo modelo) throws DAOException {
		Connection connection = null;
		ResultSet rs = null;
		PreparedStatement stmt = null;

		try {
			connection = DAOManagerMain.manager().getConnection();
			stmt = connection
					.prepareStatement("update Modelo " 
							+ "set Nombre=?, Importado=?, Id_Tipo=?, "
		               + "Id_LineaProd=?, Id_Genero=?, fechaCreacion=?, "
		               + "coleccion=?, fechaLanzamiento?"							
							+ " where id_Modelo = ? ");

         stmt.setString(1, modelo.getNombre());
         stmt.setBoolean(2, modelo.isImportado());
         stmt.setLong(3, modelo.getTipoModelo().getNroTipoModelo());
         stmt.setLong(4, modelo.getLineaProduccion().getNroLinea());
         stmt.setLong(5, modelo.getGenero().getNroGenero());
         stmt.setString(6, modelo.getFechaCreacion());
         stmt.setLong(7, modelo.getColeccion().getNroColeccion());
         stmt.setString(8, modelo.getFechaLanzamiento());
         stmt.executeUpdate();
			return 1;
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			DAOManagerMain.manager().closeAll(connection, stmt, rs);
		}
	}

	public int eliminate(Modelo modelo) throws DAOException {
		Connection connection = null;
		ResultSet rs = null;
		PreparedStatement stmt = null;

		try {
			connection = DAOManagerMain.manager().getConnection();
			stmt = connection
					.prepareStatement("delete from Modelo where id_Modelo = ? ");

						stmt.setLong(1, modelo.getNroModelo());

			stmt.executeUpdate();
			return 1;
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			DAOManagerMain.manager().closeAll(connection, stmt, rs);
		}
	}

	public ArrayList<Modelo> select() throws DAOException {
		Connection connection = null;
		ResultSet rs = null;
		PreparedStatement stmt = null;
		ArrayList<Modelo> modelos = new ArrayList<Modelo>();

		try {
			connection = DAOManagerMain.manager().getConnection();
			stmt = connection.prepareStatement("select M.*, T.*, LP.*, G.* from Modelo M,"
			       + "TipoModelo T, "
			       + "lineaproduccion LP, Genero G where M.Id_Tipo=T.Id_tipomodelo and "
			       + "M.Id_LineaProd=LP.Id_Linea and M.Id_Genero=G.Id_Genero");
			       
			rs = stmt.executeQuery();

			while (rs.next()) {
				Modelo modelo = new Modelo();
				modelo.setNroModelo(rs.getInt ("Id_Modelo"));
				modelo.setNombre(rs.getString("Nombre"));
				modelo.setImportado(rs.getBoolean("Importado"));
				modelo.setTipoModelo(new TipoModelo());
				modelo.getTipoModelo().setNroTipoModelo(rs.getInt("Id_Tipo"));
				modelo.setLineaProduccion(new LineaProduccion());
            modelo.getLineaProduccion().setNroLinea(rs.getInt("Id_LineaProd"));
            modelo.setGenero(new Genero());
            modelo.getGenero().setNroGenero (rs.getInt("Id_Genero"));
            modelo.setFechaCreacion(rs.getString("fechaCreacion"));
            modelo.setColeccion(new Coleccion());
            modelo.getColeccion().setNroColeccion(rs.getInt("coleccion"));
				modelo.setFechaLanzamiento(rs.getString("fechaLanzamiento"));

					
				modelos.add(modelo);
			}

			return modelos;
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			DAOManagerMain.manager().closeAll(connection, stmt, rs);
		}
	}

	public Modelo findById(int id) throws DAOException {
		Connection connection = null;
		ResultSet rs = null;
		PreparedStatement stmt = null;
		modelo = new Modelo();

		try {
			connection = DAOManagerMain.manager().getConnection();
			stmt = connection
					.prepareStatement("select M.*, T.*, LP.*, G.* from Modelo M,"
	                + "TipoModelo T, "
	                + "lineaproduccion LP, Genero G where M.Id_Tipo=T.Id_tipomodelo and "
	                + "M.Id_LineaProd=LP.Id_Linea and M.Id_Genero=G.Id_Genero"
	                + " where M.Id_Modelo = ?");
			stmt.setLong(1, id);
			rs = stmt.executeQuery();

			if (rs.next()) {
			   modelo.setNroModelo(rs.getInt ("Id_Modelo"));
            modelo.setNombre(rs.getString("Nombre"));
            modelo.setImportado(rs.getBoolean("Importado"));
            modelo.setTipoModelo(new TipoModelo());
            modelo.getTipoModelo().setNroTipoModelo(rs.getInt("Id_Tipo"));
            modelo.setLineaProduccion(new LineaProduccion());
            modelo.getLineaProduccion().setNroLinea(rs.getInt("Id_LineaProd"));
            modelo.setGenero(new Genero());
            modelo.getGenero().setNroGenero (rs.getInt("Id_Genero"));
            modelo.setFechaCreacion(rs.getString("fechaCreacion"));
            modelo.setColeccion(new Coleccion());
            modelo.getColeccion().setNroColeccion(rs.getInt("coleccion"));
            modelo.setFechaLanzamiento(rs.getString("fechaLanzamiento"));
			}

			return modelo;
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			DAOManagerMain.manager().closeAll(connection, stmt, rs);
		}
	}

	@Override
	public boolean existInstance(Modelo modelo) throws DAOException {
		Connection connection = null;
		ResultSet rs = null;
		PreparedStatement stmt = null;
		boolean result = false;

		try {
			connection = DAOManagerMain.manager().getConnection();
			stmt = connection
					.prepareStatement("select * from Modelo where UCASE(Nombre) = ?");

		stmt.setString(1, modelo.getNombre().toUpperCase());
//			stmt.setString(2, modelo.getApellido().toUpperCase());
			rs = stmt.executeQuery();

			if (rs.next()) {
				result = true;
			}

			return result;
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			DAOManagerMain.manager().closeAll(connection, stmt, rs);
		}
	}
}
